package com.hyjx.framework.common.util;

import com.hyjx.framework.service.SystemService;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.IndexedColors;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Date;

/**
 * excel文件导出工具类
 *
 * @author dijian
 */
@SuppressWarnings("deprecation")
public class ExcelExportUtil {
    @SuppressWarnings({"static-access", "resource"})
    public static void exportExcel(JSONArray columns, JSONArray data, JSONObject exportDataInfo, HttpServletResponse response) throws Exception {
        String fileName = "导出数据表";
        String sheetName = "导出数据表";
        String title = "导出数据表";

        //处理自定义标题、文件名、sheet页名
        if (exportDataInfo != null) {
            if (exportDataInfo.containsKey("fileName")) {
                fileName = exportDataInfo.getString("fileName");
            }
            if (exportDataInfo.containsKey("sheetName")) {
                sheetName = exportDataInfo.getString("sheetName");
            }
            if (exportDataInfo.containsKey("title")) {
                title = exportDataInfo.getString("title");
            }
        }

        String tempName = DateHelper.toString(new Date(), "yyyyMMddhhmmss"); //临时文件名称
        File temp = new File(tempName + ".xls");
        FileOutputStream fileOut = new FileOutputStream(temp); //写入excel

        HSSFWorkbook wb = new HSSFWorkbook();  //excel相关
        HSSFSheet sheet = wb.createSheet(sheetName); //创建一个excel sheet
        sheet.setDefaultColumnWidth(18);

        HSSFCellStyle style2 = wb.createCellStyle();//单元格居中对齐 加粗
        HSSFCellStyle style5 = wb.createCellStyle();//单元格居右对齐

        //设置字体
        HSSFFont font2 = wb.createFont();
        font2.setFontName("宋体"); // 字体
        font2.setColor(HSSFColor.BLACK.index);// 字体颜色
        font2.setFontHeight((short) 360); // 字高
        HSSFFont font5 = wb.createFont();
        font5.setFontName("宋体"); // 字体
        font5.setColor(HSSFColor.BLACK.index);// 字体颜色
        font5.setFontHeight((short) 200); // 字高

        style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);//指定单元格对齐
        style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//指定单元格垂直居中对齐
        style2.setWrapText(true);//指定单元格自动换行
        style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); //设置边框
        style2.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style2.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style2.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style2.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style2.setFont(font2);

        style5.setAlignment(HSSFCellStyle.ALIGN_RIGHT);//指定单元格右对齐
        style5.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//指定单元格垂直居中对齐
        style5.setWrapText(false);//指定单元格自动换行
        style5.setBorderBottom(HSSFCellStyle.BORDER_THIN); //设置边框
        style5.setBottomBorderColor(IndexedColors.BLACK.getIndex());//设置边框的颜色
        style5.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style5.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style5.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style5.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style5.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style5.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style5.setFont(font5);

        Integer currentRow = 0;
        HSSFRow row;
        HSSFCell cell;

        //拼表头
        row = sheet.createRow(currentRow++);
        row.setHeight((short) 1000);
        cell = row.createCell(0);
        cell.setCellValue(title);
        cell.setCellStyle(style2);

        //标题
        row = sheet.createRow(currentRow++);
        row.setHeight((short) 400);

        Integer currentCol = 0;

        for (int i = 0; i < columns.size(); i++) {
            JSONObject column = columns.getJSONObject(i);

            if (column.containsKey("title")) {
                title = column.getString("title");
            } else {
                title = "";
            }
            if ("".equals(title) || "操作".equals(title)) {
                continue;
            }

            cell = row.createCell(currentCol++);
            cell.setCellValue(title);
            cell.setCellStyle(style5);
        }

        //循环行
        String field = null;
        String cellValue = null;
        for (Object obj : data) {
            row = sheet.createRow(currentRow++);
            row.setHeight((short) 400);

            JSONObject rowJson = JSONObject.fromObject(obj);

            //迭代数据
            currentCol = 0;
            for (int i = 0; i < columns.size(); i++) {
                JSONObject column = columns.getJSONObject(i);

                if (column.containsKey("title")) {
                    title = column.getString("title");
                } else {
                    title = "";
                }
                if ("".equals(title) || "操作".equals(title)) {
                    continue;
                }

                field = column.getString("field");
                cell = row.createCell(currentCol++);
                cellValue = rowJson.getString(field);

                cell.setCellValue(cellValue);
                cell.setCellStyle(style5);
            }
        }
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, currentCol.intValue() - 1));//参数依次是开始行、结束行、开始列、结束列

        //写向客户端文件
        try {
            wb.write(fileOut);
            fileOut.close();

            //定义下载及文件名
            response.setHeader("pageEncoding", "utf-8");
            String agent = SystemService.getRequest().getHeader("USER-AGENT").toLowerCase();
            response.setContentType("application/vnd.ms-excel");
            String codedFileName = java.net.URLEncoder.encode(fileName, "UTF-8");
            if (agent.contains("firefox")) {
                response.setCharacterEncoding("utf-8");
                response.setHeader("content-disposition", "attachment;filename=" + new String(fileName.getBytes(), "ISO8859-1") + ".xls");
            } else {
                response.setHeader("content-disposition", "attachment;filename=" + codedFileName + ".xls");
            }

        } catch (IOException e) {
            e.printStackTrace();
        }

        ServletOutputStream out = response.getOutputStream();
        InputStream inStream = new FileInputStream(temp);// 读临时文件
        byte[] b1 = new byte[1024];
        int len;
        while ((len = inStream.read(b1)) > 0)
            out.write(b1, 0, len); // 向客户端写文件
        inStream.close();
        response.setStatus(response.SC_OK);
        response.flushBuffer();
        temp.delete();
    }
}